﻿using System;
using System.Data.SqlClient;
using System.Data;
using CommonLogging;

namespace DBBuilder.DBInteraction
{
    /// <summary>
    /// Interaction with database
    /// </summary>
    public class DBDataProvider
    {
        #region Properties

        /// <summary>
        /// Data source
        /// </summary>
        public string DataSource { get; set; }

        /// <summary>
        /// Database name
        /// </summary>
        public string DataBaseName { get; set; }

        /// <summary>
        /// Store SQL command
        /// </summary>
        public string Query { get; set; }

        /// <summary>
        /// Keep connection to database
        /// </summary>
        public SqlConnection ObjConnection { get; set; }

        /// <summary>
        /// Object Command to execute update data
        /// </summary>
        public SqlCommand ObjCommand { get; set; }

        /// <summary>
        /// A set of SqlCommand
        /// </summary>
        public SqlDataAdapter ObjDataAdapter { get; set; }

        /// <summary>
        /// The logger instance
        /// </summary>
        public static readonly ILogger Logger = LoggerManager.GetLogger(typeof(DBDataProvider).Name);

        #endregion

        #region Constructors

        /// <summary>
        /// Initialize instance of DBDataProvider based on data source and database name
        /// </summary>
        /// <param name="dataSource">DataSource</param>
        /// <param name="dataBaseName">DataBase Name</param>
        /// <param name="userName">UserName</param>
        /// <param name="password">Password</param>
        public DBDataProvider(string dataSource, string dataBaseName, string userName, string password)
        {
            this.DataSource = dataSource;
            this.DataBaseName = dataBaseName;
            this.ObjConnection =
                new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI;User ID={2};Password={3};",
                                                dataSource, dataBaseName, userName,password));
        }

        #endregion

        #region Methods

        /// <summary>
        /// Initialize sql command
        /// </summary>
        private void InitializeCommand()
        {
            if (ObjCommand == null)
            {
                try
                {
                    Logger.Info("InitializeCommand --> Start");
                   
                    this.ObjCommand = new SqlCommand(Query, ObjConnection);
                   
                    Logger.Info("InitializeCommand <-- End");
                }
                catch (Exception ex)
                {
                    Logger.ErrorFormat("InitializeCommand: {0}",
                                       ex.ToString());
                    throw new Exception(ex.ToString());
                }
            }
        }

        /// <summary>
        /// Initialize a set of command
        /// </summary>
        private void InitializeDataAdapter()
        {
            try
            {
                Logger.Info("InitializeDataAdapter --> Start");
               
                this.ObjDataAdapter = new SqlDataAdapter
                                          {
                                              SelectCommand = ObjCommand
                                          };

                Logger.Info("InitializeDataAdapter <-- End");
            }
            catch (Exception ex)
            {
                Logger.ErrorFormat("InitializeDataAdapter: {0}",
                                   ex.ToString());
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// Fill data from database to dataset
        /// </summary>
        /// <param name="ds">Dataset returned</param>
        /// <param name="tblName">Table Name</param>
        public void FillDataSet(ref DataSet ds, string tblName)
        {
            try
            {
                Logger.Info("FillDataSet --> Start");

                InitializeCommand();
                InitializeDataAdapter();
                this.ObjDataAdapter.Fill(ds, tblName);

                Logger.Info("FillDataSet <-- End");
            }
            catch (Exception ex)
            {
                Logger.ErrorFormat("FillDataSet: {0}", ex.ToString());
                throw new Exception(ex.ToString());
            }
            finally
            {
                this.ObjCommand.Dispose();
                this.ObjCommand = null;
                this.ObjDataAdapter.Dispose();
                this.ObjDataAdapter = null;
            }
        }

        #endregion
    }
}
